package ecommerce.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ecommerce.database.DataSource;
import ecommerce.model.User;
import ecommerce.model.UserTag;

public class UserTagDAOImpl implements UserTagDAO {
		
	public List<UserTag> getUsersTags() {
		PreparedStatement ps = null;
		List<UserTag> usersTags = new ArrayList<UserTag>();
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("SELECT * from users_tags;");
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				UserTag t = new UserTag();
				t.setUserId(rs.getInt(1));
				t.setTagId(rs.getInt(2));
				t.setFrequency(rs.getInt(3));
				usersTags.add(t);
			}
			rs.close();
			ps.close();
			connection.close();
			return usersTags;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public UserTag getUserTag(int user_id, int tag_id) {
		PreparedStatement ps = null;
		UserTag userTag = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("SELECT * from users_tags WHERE user_id = ? AND tag_id = ?;");
			ps.setInt(1, user_id);
			ps.setInt(2, tag_id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				userTag = new UserTag();
				userTag.setUserId(rs.getInt(1));
				userTag.setTagId(rs.getInt(2));
				userTag.setFrequency(rs.getInt(3));
			}
			rs.close();
			ps.close();
			connection.close();
			return userTag;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public UserTag getUserTagbyUser(int user_id) {
		PreparedStatement ps = null;
		UserTag userTag = new UserTag();
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("SELECT * from users_tags WHERE user_id = ?;");
			ps.setInt(1, user_id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				userTag.setUserId(rs.getInt(1));
				userTag.setTagId(rs.getInt(2));
				userTag.setFrequency(rs.getInt(3));
			}
			rs.close();
			ps.close();
			connection.close();
			return userTag;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public List<UserTag> getUserTagbyUserPassword(String user_name, String password) {
		PreparedStatement ps = null;
		List<UserTag> userTagList = new ArrayList<UserTag>();
		try {
			Connection connection = DataSource.getInstance().getConnection();
			User user = new UserDAOImpl().authenticate(user_name, password);
			if( user != null)
			{
				ps = connection.prepareStatement("SELECT * from users_tags WHERE user_id = ?;");
				ps.setInt(1, user.getUserId());
				ResultSet rs = ps.executeQuery();
				while(rs.next())
				{
					UserTag userTag = new UserTag();
					userTag.setUserId(rs.getInt(1));
					userTag.setTagId(rs.getInt(2));
					userTag.setFrequency(rs.getInt(3));
					userTag.setTagDescription(new TagDAOImpl().getTagById(rs.getInt(2)).getTagDescription());
					userTagList.add(userTag);
				}
				rs.close();
				ps.close();
				connection.close();
				return userTagList;
			}
			else
			{
				return null;
			}
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public List<UserTag> getTopTenUserTag(int user_id) {
		PreparedStatement ps = null;
		List<UserTag> usersTags = new ArrayList<UserTag>();
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("SELECT * from users_tags WHERE user_id = ? ORDER BY frequency DESC LIMIT 0, 10;");
			ps.setInt(1, user_id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				UserTag tag = new UserTag();
				tag.setUserId(rs.getInt(1));
				tag.setTagId(rs.getInt(2));
				tag.setFrequency(rs.getInt(3));
				usersTags.add(tag);
			}
			rs.close();
			ps.close();
			connection.close();
			return usersTags;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public UserTag getUserTagbyTag(int tag_id) {
		PreparedStatement ps = null;
		UserTag userTag = new UserTag();
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("SELECT * from users_tags WHERE tag_id = ?;");
			ps.setInt(1, tag_id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				userTag.setUserId(rs.getInt(1));
				userTag.setTagId(rs.getInt(2));
				userTag.setFrequency(rs.getInt(3));
			}
			rs.close();
			ps.close();
			connection.close();
			return userTag;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public boolean insertUserTag(int user_id, int tag_id) {
		PreparedStatement ps = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("INSERT into users_tags(user_id, tag_id, frequency) VALUES(?, ?, ?);");
			ps.setInt(1, user_id);
			ps.setInt(2, tag_id);
			ps.setInt(3, 0);
			ps.executeUpdate();
			ps.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			return false;
		}
	}
	
	public boolean updateUserTagFrequency(int user_id, int tag_id) {
		PreparedStatement ps = null;
		UserTag current_data = getUserTag(user_id, tag_id);
		int newFrequency = 0;
		if(current_data == null)
		{
			insertUserTag(user_id, tag_id);
			newFrequency = 1;
		}
		else
		{
			newFrequency = (current_data.getFrequency() + 1);
		}
		try {			
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("UPDATE users_tags SET frequency = ? WHERE user_id = ? AND tag_id = ?;");
			ps.setInt(1, newFrequency);
			ps.setInt(2, user_id);
			ps.setInt(3, tag_id);
			ps.executeUpdate();
			ps.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			return false;
		}
	}
	
	public ArrayList<ArrayList<Integer>> getUserTagSumFrequency(int start) {
		PreparedStatement ps = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ArrayList<ArrayList<Integer>> tagsFrequency = new ArrayList<ArrayList<Integer>>();
			ps = connection.prepareStatement("SELECT tag_id, SUM(frequency) as freq FROM users_tags GROUP BY tag_id ORDER BY freq DESC LIMIT " + start + ", 20;");
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				ArrayList<Integer> tempList = new ArrayList<Integer>();
				tempList.add(rs.getInt(1));
				tempList.add(rs.getInt(2));
				tagsFrequency.add(tempList);
			}
			rs.close();
			ps.close();
			connection.close();
			return tagsFrequency;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public int getTotalTags() {
		PreparedStatement ps = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("SELECT tag_id, SUM(frequency) as freq FROM users_tags GROUP BY tag_id ORDER BY freq DESC;");
			ResultSet rs = ps.executeQuery();
			int counter  = 0;
			while(rs.next()) {
				counter++;
			}
			rs.close();
			ps.close();
			connection.close();
			return counter;
		} catch (SQLException ex) {
			return 0;
		}
	}
}
